package com.dcivision.dms;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Hashtable;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class LoadDocumentData {

  public static final String REVISION = "$Revision: 1.10.2.1 $";

  static String author = null;
  static String srcPath = null;
  static String driverStr = null;
  static String dbConnStr = null;
  static String dbUsername = null;
  static String dbPassword = null;

  static String packageName = "com.dcivision.";

  static final String JAVA_INTEGER = "Integer";
  static final String JAVA_DECIMAL = "Float";
  static final String JAVA_STRING = "String";
  static final String JAVA_DATE = "Timestamp";

  static Connection conn = null;
  static String sqlStat = null;
  static Statement stat = null;
  static ResultSet rs = null;
  static ResultSetMetaData rsmd = null;

  static String sUdfID = null;
  static String sUdfDetailID = null;
  
  private static final Log log = LogFactory.getLog(LoadDocumentData.class);
  public static void main(String args[]) throws Exception{

    java.util.Date today = new java.util.Date();

    String sqlDoc = "";
    String sqlVersion = "";
    String sqlDocVersion = "";
    String sqlContent = "";
    String sqlDocDetail = "";
    String sqlRoot = "";

    // Load properties in the "src" directory.
    java.util.Properties props = new java.util.Properties();
    String path = new LoadDocumentData().getClass().getProtectionDomain().getCodeSource().getLocation().toString().substring(6);
    if (!path.endsWith("/")) {
      path += "/";
    }
    path += "../../../src/generate.properties";
    java.io.FileInputStream fis = new java.io.FileInputStream(new java.io.File(path));
    props.load(fis);

    author = props.getProperty("author");
    srcPath = props.getProperty("srcPath");
    driverStr = props.getProperty("driverStr");
    dbConnStr = props.getProperty("dbConnStr");
    dbUsername = props.getProperty("dbUsername");
    dbPassword = props.getProperty("dbPassword");
    // Initialize database connection.
    openDBConn();

    stat = conn.createStatement();

    //Get Table Key
    Hashtable htTableKey = new Hashtable();
    htTableKey = getTableKey(conn);

    //Get UDF ID
    genUserDefinedIndex(conn, htTableKey);

    //Create data
    Integer ownerID = new Integer(0);
    Integer nDocID =  new Integer(1);
    Integer nDocDetailID =  new Integer(1);
    Integer nVersionID =  new Integer(1);
    Integer nContentID =  new Integer(1);
    Integer nDmsRootID =  new Integer(1);
    Integer nMtmDocVersionID =  new Integer(1);

    Integer nParentID = null;
    Integer nRootID = null;
    String sConvertedName = "1122334455";
    if (htTableKey.containsKey("DMS_DOCUMENT")) {
      nDocID = new Integer(((Integer)htTableKey.get("DMS_DOCUMENT")).intValue() + 1);
    }
    if (htTableKey.containsKey("DMS_DOCUMENT_DETAIL")) {
      nDocDetailID = new Integer(((Integer)htTableKey.get("DMS_DOCUMENT_DETAIL")).intValue() + 1);
    }
    if (htTableKey.containsKey("DMS_VERSION")) {
      nVersionID = new Integer(((Integer)htTableKey.get("DMS_VERSION")).intValue() + 1);
    }
    if (htTableKey.containsKey("DMS_CONTENT")) {
      nContentID = new Integer(((Integer)htTableKey.get("DMS_CONTENT")).intValue() + 1);
    }
    if (htTableKey.containsKey("DMS_ROOT")) {
      nDmsRootID = new Integer(((Integer)htTableKey.get("DMS_ROOT")).intValue() + 1);
    }
    if (htTableKey.containsKey("MTM_DOCUMENT_VERSION")) {
      nMtmDocVersionID = new Integer(((Integer)htTableKey.get("MTM_DOCUMENT_VERSION")).intValue() + 1);
    }
    System.out.println("start value: " +  " DocumentID "  + nDocID + " DocDetailID "+ nDocDetailID + " DocVersion "+ nVersionID + " DocContent "+ nContentID + " nMtmDocVersionID " +nMtmDocVersionID);
    System.out.println("udf: " + sUdfID+ " "  + sUdfDetailID);

    int nStart = (new Integer(args[0])).intValue();
    int nEnd = (new Integer(args[1])).intValue();

    nParentID = new Integer(args[2]);
    Integer nRootParentID = new Integer(args[2]);
    nRootID = new Integer(args[3]);
    System.out.println("rootID : " + nRootID+ "  ParentID " + nParentID);

    String sFieldValue = "";
    PreparedStatement preStat = null;
    String sDocName = "";
    int count = 0;
    int total = 0;

    for (int i=nStart; i<=nEnd; i++) {

      try
      {
      sFieldValue = "REF" + i;
      sDocName = Calendar.getInstance().getTimeInMillis() + ".tif";
      //Create Document Folder by 500 each
      if (count%500 == 0) {

        sqlDoc = "INSERT INTO DMS_DOCUMENT VALUES(" + nDocID.toString() + ",'" + "TestDocument"+i + "','F'," + nRootParentID + "," + nRootID.toString() + ", 'S', '" + formatDate(today) + "', NULL, '"+ownerID+"','Ref Num', 'desc', 0, 'A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,'A',0,0,'"+ formatDate(today) +"',0,'"+ formatDate(today) +"')";
        preStat = conn.prepareStatement(sqlDoc);

        preStat.executeUpdate();

        //sqlRoot = "INSERT INTO DMS_ROOT VALUES(" + nDmsRootID.toString() + ",'" + i + "','U',0,0,'"+ formatDate(today) +"',0,'"+ formatDate(today) +"')";
        //preStat = conn.prepareStatement(sqlRoot);
        //preStat.executeUpdate();

        nParentID = nDocID;
        nDocID = new Integer(nDocID.intValue() + 1);
      }

      total = count + nStart;
      System.out.println("xxx Count: " + total + " docID = " +nDocID);

      //Create dmsDocument
      sqlDoc = "INSERT INTO DMS_DOCUMENT VALUES(" + nDocID.toString() + ",'" + "TestDocument"+i+ ".tif" + "','D'," + nParentID.toString() + "," + nRootID.toString() + ", 'S','"+ formatDate(today) +"', NULL, '"+ownerID+"','Ref Num', 'desc', 20480, 'A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'A',0,0,'"+ formatDate(today) +"',0,'"+ formatDate(today) +"')";
      preStat = conn.prepareStatement(sqlDoc);
      preStat.executeUpdate();

      sqlDocDetail = "INSERT INTO DMS_DOCUMENT_DETAIL VALUES(" + nDocDetailID.toString() + "," + nDocID.toString() + "," + sUdfID + "," + sUdfDetailID + ",'" + sFieldValue + "', null, null, 'A',0,0,'"+ formatDate(today) +"',0,'"+ formatDate(today) +"')";
      preStat = conn.prepareStatement(sqlDocDetail);
      preStat.executeUpdate();

      sqlContent = " INSERT INTO DMS_CONTENT VALUES (" + nContentID.toString() + "," + sConvertedName + ", 'IMAGE', null, 'TIF', 'A', 0,0,'"+ formatDate(today) +"',0,'"+ formatDate(today) +"')";
      preStat = conn.prepareStatement(sqlContent);
      preStat.executeUpdate();

      sqlVersion = "INSERT INTO DMS_VERSION VALUES(" + nVersionID.toString() + ", "+nDocID.toString()+", 1,'ROOT',0," + nContentID.toString() + ",0, 'Ref Num', 'desc', 20480, 'A', null, 'A',0,0,'"+ formatDate(today) +"',0,'"+ formatDate(today) +"')";
      preStat = conn.prepareStatement(sqlVersion);
      preStat.executeUpdate();

      sqlDocVersion = "INSERT INTO MTM_DOCUMENT_VERSION VALUES(" + nMtmDocVersionID.toString() + "," + nDocID.toString() + "," + nVersionID.toString() + ",'A', 0, 0,'"+ formatDate(today) +"',0,'"+ formatDate(today) +"')";
      preStat = conn.prepareStatement(sqlDocVersion);
      preStat.executeUpdate();

      nDocID = new Integer(nDocID.intValue() + 1);
      nDocDetailID = new Integer(nDocDetailID.intValue() + 1);
      nVersionID = new Integer(nVersionID.intValue() + 1);
      nContentID = new Integer(nContentID.intValue() + 1);
      nDmsRootID = new Integer(nDmsRootID.intValue() + 1);
      nMtmDocVersionID = new Integer(nMtmDocVersionID.intValue() + 1);

    }catch (Exception ee) {
      log.error(ee, ee);
    }finally {
      try { preStat.close(); } catch (Exception ep) {}
    }
    count++;
    }

    //update table key
    PreparedStatement statment=null;
    if (htTableKey.containsKey("DMS_DOCUMENT")) {
      statment = conn.prepareStatement("UPDATE SYS_TABLE_KEY SET TABLE_KEY_MAX="+ nDocID.toString() +" WHERE TABLE_NAME='DMS_DOCUMENT'");
      statment.executeUpdate();
    }else {
      statment = conn.prepareStatement("INSERT INTO SYS_TABLE_KEY VALUES('DMS_DOCUMENT', "+ nDocID.toString() +")");
      statment.executeUpdate();
    }
    if (htTableKey.containsKey("DMS_DOCUMENT_DETAIL")) {
      statment = conn.prepareStatement("UPDATE SYS_TABLE_KEY SET TABLE_KEY_MAX="+ nDocDetailID.toString() +" WHERE TABLE_NAME='DMS_DOCUMENT_DETAIL'");
      statment.executeUpdate();
    }else{
      statment = conn.prepareStatement("INSERT INTO SYS_TABLE_KEY VALUES('DMS_DOCUMENT_DETAIL', "+ nDocDetailID.toString()+")");
      statment.executeUpdate();

    }
    if (htTableKey.containsKey("DMS_VERSION")) {
      statment = conn.prepareStatement("UPDATE SYS_TABLE_KEY SET TABLE_KEY_MAX="+ nVersionID.toString() +" WHERE TABLE_NAME='DMS_VERSION'");
      statment.executeUpdate();
    }else{
      statment = conn.prepareStatement("INSERT INTO SYS_TABLE_KEY VALUES('DMS_VERSION', "+ nVersionID.toString()+")");
      statment.executeUpdate();
    }
    if (htTableKey.containsKey("DMS_CONTENT")) {
      statment = conn.prepareStatement("UPDATE SYS_TABLE_KEY SET TABLE_KEY_MAX="+ nContentID.toString() +" WHERE TABLE_NAME='DMS_CONTENT'");
      statment.executeUpdate();
    }else{
      statment = conn.prepareStatement("INSERT INTO SYS_TABLE_KEY VALUES('DMS_CONTENT', "+ nContentID.toString()+")");
      statment.executeUpdate();
    }
    if (htTableKey.containsKey("MTM_DOCUMENT_VERSION")) {
      statment = conn.prepareStatement("UPDATE SYS_TABLE_KEY SET TABLE_KEY_MAX="+ nMtmDocVersionID.toString() +" WHERE TABLE_NAME='MTM_DOCUMENT_VERSION'");
      statment.executeUpdate();
    }else{
      statment = conn.prepareStatement("INSERT INTO SYS_TABLE_KEY VALUES('MTM_DOCUMENT_VERSION', "+ nMtmDocVersionID.toString()+")");
      statment.executeUpdate();
    }
    statment.close();

    System.out.println("final value: " +  " DocumentID "  + nDocID + " DocDetailID "+ nDocDetailID + " DocVersion "+ nVersionID + " DocContent "+ nContentID +" nMtmDocVersionID "+nMtmDocVersionID);

    // Initialize database connection.
    closeDBConn();
  }

  public static void genUserDefinedIndex(Connection conn, Hashtable htTableKey) throws Exception {
    java.util.Date today = new java.util.Date();
    String sqlItem1 = "";
    int nUdfID = 1;
    int nUdfDetailID = 1;
    if (htTableKey.containsKey("SYS_USER_DEFINED_INDEX")) {
      nUdfID = ((Integer) htTableKey.get("SYS_USER_DEFINED_INDEX")).intValue() + 1;
    }
    if (htTableKey.containsKey("SYS_USER_DEFINED_INDEX_DETAIL")) {
      nUdfDetailID = ((Integer) htTableKey.get("SYS_USER_DEFINED_INDEX_DETAIL")).intValue() + 1;
    }
    sUdfID = (new Integer(nUdfID)).toString();
    sUdfDetailID = (new Integer(nUdfDetailID)).toString();


    StringBuffer sbUdf = new StringBuffer();
    sbUdf.append("SELECT ID, USER_DEFINED_TYPE FROM SYS_USER_DEFINED_INDEX WHERE USER_DEFINED_TYPE = 'Document Reference' ");


    PreparedStatement preStatUdf = null;
    ResultSet rsUdf = null;
    preStatUdf = conn.prepareStatement(sbUdf.toString());
    rsUdf = preStatUdf.executeQuery();

    if (rsUdf.next()) {
      sUdfID = (new Integer(rsUdf.getInt("ID"))).toString();
      String detail = "SELECT ID FROM SYS_USER_DEFINED_INDEX_DETAIL WHERE USER_DEFINED_ID = " + sUdfID ;
      PreparedStatement preStatUdfDetail = conn.prepareStatement(detail);
      ResultSet rsUdfDetail = preStatUdfDetail.executeQuery();
      if (rsUdfDetail.next()) {
        sUdfDetailID = (new Integer(rsUdfDetail.getInt("ID"))).toString();
      }
      preStatUdfDetail.close();
      rsUdfDetail.close();
    }else { //insert
      System.out.println("Inside create udf.....");
      String insertUdf = sqlItem1 = "INSERT INTO SYS_USER_DEFINED_INDEX VALUES(" + sUdfID + ", 0, 'D', 'Document Reference','Document Reference', 'N', '0', 'A', 0, 0,'"+ formatDate(today) +"',0,'"+ formatDate(today) +"')";
      String insertUdfDetail = sqlItem1 = "INSERT INTO SYS_USER_DEFINED_INDEX_DETAIL VALUES(" + sUdfDetailID + "," + sUdfID + ",'Reference Number','Y', 'I', 'A', 0, 0, '"+ formatDate(today) +"',0,'"+ formatDate(today) +"')";
      preStatUdf = conn.prepareStatement(insertUdf);
      PreparedStatement preStatUdfDetail = conn.prepareStatement(insertUdfDetail);
      preStatUdf.executeUpdate();
      preStatUdfDetail.executeUpdate();

      //preStatUdf.close();
      preStatUdfDetail.close();
    }
    preStatUdf.close();
    rsUdf.close();
    System.out.println("UDF = " + sUdfID + " detail = " + sUdfDetailID);
  }

  public static Hashtable getTableKey(Connection conn)  throws Exception{
    //Get Table Key
    StringBuffer sbTableKey = new StringBuffer();
    sbTableKey.append("SELECT * FROM SYS_TABLE_KEY");
    Hashtable htTableKey = new Hashtable();

    PreparedStatement preStatTableKey = null;
    ResultSet rsTableKey = null;

    preStatTableKey = conn.prepareStatement(sbTableKey.toString());
    rsTableKey = preStatTableKey.executeQuery();

    while (rsTableKey.next()) {
      //System.out.println(rsTableKey.getString(1).trim()+ " : " + rsTableKey.getInt(2));
      htTableKey.put(rsTableKey.getString(1).trim(),new Integer(rsTableKey.getInt(2)));
      System.out.println("table key = " + rsTableKey.getString(1).trim() + "  " +new Integer(rsTableKey.getInt(2)));
    }

    preStatTableKey.close();
    rsTableKey.close();

    return htTableKey;
  }

  public static void openDBConn() throws Exception {
    Class.forName(driverStr);
    conn = DriverManager.getConnection(dbConnStr, dbUsername, dbPassword);
  }

  public static void closeDBConn() throws Exception {
    try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
    try { stat.close(); } catch (Exception ignore) {} finally { stat = null; }
    try { conn.close(); } catch (Exception ignore) {} finally { conn = null; }
  }

  protected static SimpleDateFormat getDateFormat() {
    return new SimpleDateFormat("MM/dd/yyyy");
  }

  /**
   * Call this function to format a java.util.Date into a String
   * with format "dd/mm/yyyy"
   */
  public static String formatDate(java.util.Date date) {
    if (date == null)
      return "";
    String dateStr = getDateFormat().format(date);
    return dateStr.replace('-', '/');
  }

}